import pymysql
import numpy
from numpy import array
import pandas
import matplotlib.pylab
import jieba
import wordcloud
from PIL import Image

conn=pymysql.connect(host="127.0.0.1",user="root",passwd="root",db="hexun")
sql="select * from myhexun"
data=pandas.read_sql(sql,conn)

#词云图
alldata=""
for i in data.columns:
    for j in range(0,len(data)):
        cutdata=jieba.cut(data["name"][j])
        for z in cutdata:
            alldata=alldata+" "+str(z)
font=r"C:\WINDOWS\Fonts\simhei.ttf"
yun=Image.open("F:\\pytmp\\hxbk\\cytyong.png")
yunarray=array(yun)
cyt=wordcloud.WordCloud(collocations=False,font_path=font,mask=yunarray,background_color="white").generate(alldata)
#matplotlib.pylab.subplot(2,3,1)
matplotlib.pylab.imshow(cyt)
matplotlib.pylab.show()

#初步展示
data2=data.T
hits=data2.values[3]
comments=data2.values[4]
#matplotlib.pylab.subplot(2,3,2)
matplotlib.pylab.title("Preliminary data display")
matplotlib.pylab.plot(hits,comments,"o")
matplotlib.pylab.show()
#异常处理
line=len(data.values)
col=len(data.values[0])
da=data.values
x=0
for i in range(0,line):
    for j in range(0,col):
        if(da[i][3]>2750):
            continue
        elif(da[i][4]>25):
            continue
        elif(da[i][3]<100 and da[i][4]>7):
            continue
        else:
            if(x==0):
                da2=da[i]
            else:
                da2=numpy.row_stack((da2,da[i]))
            x=x+1
da3=da2.T
hits=da3[3]
comments=da3[4]
#matplotlib.pylab.subplot(2,3,3)
matplotlib.pylab.title("After data exception handling")
matplotlib.pylab.plot(hits,comments,"o")
matplotlib.pylab.show()
#点击量直方图
hitsty=numpy.arange(0,2750,250)
a=da3[3].tolist()
a=numpy.array(a).astype(int)
#matplotlib.pylab.subplot(2,3,2)
matplotlib.pylab.title("Clicking histogram")
matplotlib.pylab.hist(a,hitsty)
matplotlib.pylab.show()
#评论直方图
commentsty=numpy.arange(0,25,3)
b=da3[4].tolist()
b=numpy.array(b).astype(int)
#matplotlib.pylab.subplot(2,3,3)
matplotlib.pylab.title("comment histogram")
matplotlib.pylab.hist(b,commentsty)
matplotlib.pylab.show()

